﻿CREATE PROCEDURE [dbo].[sp_Insert_Zips]

AS
	BEGIN TRAN T1
	
	declare @count int
	declare @start int
	declare @startDivision varchar(2)
	declare @division varchar(2)
	declare @account varchar(4)
	declare @currentMonth int
	declare @currentYear int
	
	select @count = count(*) from [dbo].[DivisionAdmin]
	set @start = 1
	set @startDivision = 0
	
	while @start < @count + 1
	begin
		select top (1) @division = DivisionNum, @account = AccountID from [dbo].[DivisionAdmin] 
		where DivisionNum > @startDivision order by DivisionNum asc
		
		update dbo.Leads set MdNumber = @account
		where Division = @division and MdNumber in ('UKNW', 'UNKN')
		
		set @startDivision = @division
		set @start = @start + 1
	end
	
	Insert into dbo.Zips
	select l.ZipRequesterCode from Leads l
	left join dbo.Zips z 
	on l.ZipRequesterCode = z.Zip
	where z.Zip is null
	group by l.ZipRequesterCode
	
	truncate table ZipInfo
	truncate table dbo.OptimizedLeads
	
	insert into ZipInfo
	select z.ZipID, substring(z.Zip, 1, 5) as ZipCode,
			sum(case when l.LeadID is not null and l.PcLeadType = 'P' and l.PersonStatus = 'A' then 1 else 0 end) as ActiveLeadCount,
			sum(case when l.LeadID is not null and l.PcLeadType = 'P' and l.PersonStatus = 'L' then 1 else 0 end) as LapsedLeadCount,
			sum(case when l.LeadID is not null and l.LeadType = 'B' then 1 else 0 end) as BusinesLeadCount,
			sum(case when l.LeadID is not null and l.LeadType = 'N' then 1 else 0 end) as NeighborLeadCount,
			max(l.State), max(l.PreferCity), max(l.County), l.Division, l.Market, l.Territory, max(l.MdNumber), 
			max(l.Latitude), max(l.Longitude), NULL,
			sum(case when l.LeadID is not null and l.LeadType = 'A' then 1 else 0 end) as ActiveStopCount,
			sum(case when l.LeadID is not null and l.LeadType = 'L' then 1 else 0 end) as LapsedStopCount,
			max(l.City), case when COUNT(distinct l.PcLeadType) = 1 then MAX(l.PcLeadType) else 'M' end
			from dbo.Zips z inner join dbo.Leads l 
			on l.ZipRequesterCode = z.Zip
			group by z.ZipID, z.Zip, l.Division, l.Market, l.Territory

	select @currentMonth = MAX(MonthName) from MonthSetting
		where EndDate >= GETDATE() and StartDate <= GETDATE()

	select @currentYear = CurrentYear from CommonSetting

	update ZipInfo 
		set ActiveLeadCount = zt.ActiveCount, LapsedLeadCount = zt.LapseCount
		from ZipInfo zi
		inner join
		(select zi.ZipID,
		sum(case when l.PcLeadType = 'R' and l.PersonStatus = 'A' then 1 else 0 end) as ActiveCount,
		sum(case when l.PcLeadType = 'R' and l.PersonStatus = 'L' then 1 else 0 end) as LapseCount
		from ZipInfo zi
		inner join Zips z
		on zi.ZipID = z.ZipID
		inner join Leads l
		on l.ZipRequesterCode = z.Zip
		where zi.ActiveLeadCount = 0 and zi.LapsedLeadCount = 0
		group by zi.ZipID) zt
		on zi.ZipID = zt.ZipID
	
	update ZipInfo set AgentName = ad.AgentName
		from ZipInfo zi
		inner join AssignmentDetail ad
		on zi.ZipID = ad.ZipID
		where ad.AgentName is not null 
		and ad.AgentName <> ''
		and ad.PeriodMonth = @currentMonth 
		and ad.PeriodYear = @currentYear
	
	IF (  @@ERROR <> 0 )
	BEGIN
		ROLLBACK TRAN T1
	END
	ELSE
	BEGIN
		COMMIT TRAN T1
	END

RETURN 0;